# -*- coding: utf-8 -*-

"""
Datetime: 2020/03/19
Author: Zhang Yafei
Description: 
"""
import string
from jieba.analyse import extract_tags
from pandas import read_csv

from DBHelper import db


def get_china_history_data():
    sql = """
      select sum(confirm), (select suspect from china_history order by date desc limit 1), sum(heal),sum(dead) from china_lasted 
      where update_time=(select update_time from china_lasted 
      order by update_time desc 
      limit 1);
    """
    res = db.fetchone(sql)
    return res


def get_china_province_lasted_data():
    sql = '''
      select province, sum(confirm),sum(suspect),sum(dead),sum(heal) from china_lasted 
      GROUP BY province;
    '''
    res_list = db.fetchall(sql)
    data = []
    for res in res_list:
        data.append({'name': res[0], 'value': int(res[1]), 'suspect': int(res[2]), 'dead': int(res[3]), 'heal': int(res[4])})
    return data


def get_china_total_data():
    sql = "select date, confirm, suspect, dead, heal from china_history;"
    res_list = db.fetchall(sql)
    days, confirm, suspect, dead, heal = [], [], [], [], []
    for res in res_list:
        if type(res[0]) == str:
            days.append(res[0].replace('2020-', ''))
        else:
            days.append(res[0].strftime('%m-%d'))
        confirm.append(res[1])
        suspect.append(res[2])
        dead.append(res[3])
        heal.append(res[4])
    return days, confirm, suspect, dead, heal

def get_world_lasted_data():
    # sql = """
        #   select sum(confirm),sum(confirm_now),sum(heal),sum(dead) from world_lasted;
        #   """
    sql = "SELECT confirm, now_confirm,heal, dead FROM world_statis"
    res = db.fetchone(sql)
    return res


def get_world_country_lasted_data():
    sql = '''
      select country_english_name, confirm,dead,heal from world_lasted 
      where country_english_name != '' GROUP BY country;
    '''
    res_list = db.fetchall(sql)
    china_res = db.fetchone('select sum(confirm), sum(dead), sum(heal) from china_lasted;')
    data = []
    for res in res_list:
        data.append({'name': res[0], 'value': int(res[1]), 'dead': int(res[2]), 'heal': int(res[3])})
    data.append({'name': 'China', 'value': int(china_res[0]), 'dead': int(china_res[1]), 'heal': int(china_res[2])})
    return data


def get_world_total_data():
    sql = "select date, confirm, dead, heal from world_history;"
    res_list = db.fetchall(sql)
    days, confirm, dead, heal = [], [], [], []
    for res in res_list:
        if type(res[0]) == str:
            days.append(res[0].replace('2020-', ''))
        else:
            days.append(res[0].strftime('%m-%d'))
        confirm.append(res[1])
        dead.append(res[2])
        heal.append(res[3])
    return days, confirm, dead, heal


def get_china_add_data():
    sql = "select date, confirm_add, suspect_add, dead_add, heal_add from china_history where confirm_add is not null;"
    res_list = db.fetchall(sql)
    days, confirm_add, suspect_add, dead_add, heal_add = [], [], [], [], []
    for res in res_list:
        if type(res[0]) == str:
            days.append(res[0].replace('2020-', ''))
        else:
            days.append(res[0].strftime('%m-%d'))
        confirm_add.append(res[1])
        suspect_add.append(res[2])
        dead_add.append(res[3])
        heal_add.append(res[4])
    return days, confirm_add, suspect_add, dead_add, heal_add


def get_world_add_data():
    sql = "select date, confirm_add from world_history;"
    res_list = db.fetchall(sql)
    days, confirm_add = [], []
    for res in res_list:
        if type(res[0]) == str:
            days.append(res[0].replace('2020-', ''))
        else:
            days.append(res[0].strftime('%m-%d'))
        confirm_add.append(res[1])
    return days, confirm_add


def get_nohubei_top5():
    sql = '''
    select city, confirm from china_lasted where province not in ('湖北', '北京','上海', '天津','重庆')  
    union all
    select province as city,sum(confirm) from china_lasted where province in ('北京','上海', '天津','重庆') 
    group by province 
    ORDER BY confirm desc limit 5;
    '''
    res = db.fetchall(sql)
    city_list, confirm_list = [],[]
    for city, confirm in res:
        city_list.append(city)
        confirm_list.append(int(confirm))
    return city_list, confirm_list


def get_world_top5():
    sql = "select country, confirm from world_lasted ORDER BY confirm desc limit 5;"
    res = db.fetchall(sql)
    city_list, confirm_list = [],[]
    for city, confirm in res:
        city_list.append(city)
        confirm_list.append(int(confirm))
    return city_list, confirm_list


def get_hotsearch():
    sql = "select content from hotsearch ORDER BY id desc limit 20;"
    res = db.fetchall(sql)
    data = []
    for content in res:
        text = content[0].rstrip(string.digits)
        value = content[0][len(text):]
        words = extract_tags(text)
        for word in words:
            if not word.isdigit():
                data.append({'name': word, 'value': value})
    return data


if __name__ == '__main__':
    # get_china_history_data()
    # get_china_province_lasted_data()
    # print(get_china_add_data())
    # print(get_china_total_data())
    # print(get_nohubei_top5())
    # get_hotsearch()
    print(get_world_lasted_data())



